from datetime import datetime

from fastapi import FastAPI, Depends, Request
from fastapi.responses import HTMLResponse
from fastapi.templating import Jinja2Templates
from fastapi.staticfiles import StaticFiles
from fastapi.middleware.cors import CORSMiddleware
from sqlalchemy import Column, Integer, String, create_engine, func
from sqlalchemy import or_, and_
from sqlalchemy.sql.expression import null
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy.orm import declarative_base
from fastapi_sqlalchemy import DBSessionMiddleware, db
import uvicorn

from models import cninfo_api_diaoyan, report_deep, wechat_sec, year_report, sseinfo_report, sseinfo_iwen, cninfo_iwen
from utils.icon_manager import icon_manager

Base = declarative_base()

app = FastAPI()
app.add_middleware(
    DBSessionMiddleware,
    # db_url="mysql+pymysql://root:123456@localhost/database"
#    db_url="sqlite:///C:/Users/Administrator/Downloads/database.sqlite"
    db_url="sqlite:///./database.sqlite"
)
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=False,
    allow_methods=["*"],
    allow_headers=["*"],
)
app.mount("/static", StaticFiles(directory="static"), name="static")
templates = Jinja2Templates(directory="templates")


async def get_db_session():
    try:
        yield db.session
    finally:
        db.session.close()


@app.get("/api")
@app.post("/api")
async def diapyan(page: int = 1, limit: int = 100, db_session: Session = Depends(get_db_session)):
    offset = (page - 1) * limit
    results = db_session.query(cninfo_api_diaoyan).offset(
        offset).limit(limit).all()
    total = db_session.query(func.count(cninfo_api_diaoyan.aid)).scalar()
    pages = int(total / limit)
    return {
        "code": 200,
        "limit": limit,
        "total": total,
        "pages": pages,
        "data": [result.__dict__ for result in results]
    }

# Todo
# 添加板块分类的导航   沪市主板 科创板  深市主板 中小板   北交所
# 添加板块


@app.get("/", response_class=HTMLResponse)
@app.post("/", response_class=HTMLResponse)
async def cninfo_index(request: Request, page: int = 1, limit: int = 20, market: str = "sz", db_session: Session = Depends(get_db_session)):
    offset = (page - 1) * limit
    data = []
    if market == "sh":
        # 沪市：读取 sseinfo_report
        results = db_session.query(sseinfo_report).order_by(sseinfo_report.stock_report_timestamp.desc()).offset(
            offset).limit(limit).all()
        total = db_session.query(func.count(sseinfo_report.id)).scalar()
        for r in results:
            url = getattr(r, "stock_report_url", "") or ""
            lower_url = url.lower()
            ext = ""
            if ".pdf" in lower_url:
                ext = "pdf"
            elif ".docx" in lower_url:
                ext = "docx"
            elif ".doc" in lower_url:
                ext = "doc"
            data.append({
                "secCode": getattr(r, "stock_code", ""),
                "secName": getattr(r, "stock_name", ""),
                "announcementTitle": getattr(r, "stock_report_name", ""),
                "adjunctUrl": url,
                "announcementTime": getattr(r, "stock_report_timestamp", 0),
                "fileType": ext,
            })
    else:
        # 深市：读取 cninfo
        results = db_session.query(cninfo_api_diaoyan).order_by(cninfo_api_diaoyan.announcementTime.desc()).offset(
            offset).limit(limit).all()
        total = db_session.query(func.count(cninfo_api_diaoyan.aid)).scalar()
        for r in results:
            url = getattr(r, "adjunctUrl", "") or ""
            lower_url = url.lower()
            ext = getattr(r, "adjunctType", "") or ""
            if not ext:
                if ".pdf" in lower_url:
                    ext = "pdf"
                elif ".docx" in lower_url:
                    ext = "docx"
                elif ".doc" in lower_url:
                    ext = "doc"
            data.append({
                "secCode": getattr(r, "secCode", ""),
                "secName": getattr(r, "secName", ""),
                "announcementTitle": getattr(r, "announcementTitle", ""),
                "adjunctUrl": url,
                "announcementTime": getattr(r, "announcementTime", 0),
                "fileType": (ext or "").lower(),
            })

    pages = int(total / limit)
    return templates.TemplateResponse("report_diaoyan.html", {"request": request, "data": data, "page": page, "pages": pages, "market": market})


@app.get('/c/{stock_code:str}')
async def stock_report(request: Request, stock_code: str, page: int = 1, limit: int = 10, db_session: Session = Depends(get_db_session)):
    if len(stock_code) != 6 or not stock_code.isdigit():
        return "Invalid stock code"
    
    # 确定市场
    market = 'sh' if stock_code.startswith(('60', '68')) else 'sz'
    
    offset = (page - 1) * limit
    
    # 查询调研报告数据
    if market == 'sh':
        report_rows = db_session.query(sseinfo_report).filter(sseinfo_report.stock_code == stock_code).order_by(sseinfo_report.stock_report_timestamp.desc()).offset(offset).limit(limit).all()
        total = db_session.query(func.count(sseinfo_report.id)).filter(sseinfo_report.stock_code == stock_code).scalar()
        report_data = [{
            'secCode': r.stock_code,
            'secName': r.stock_name,
            'announcementTitle': r.stock_report_name,
            'adjunctUrl': r.stock_report_url,
            'announcementTime': r.stock_report_timestamp
        } for r in report_rows]
    else:
        report_rows = db_session.query(cninfo_api_diaoyan).filter(cninfo_api_diaoyan.secCode == stock_code).order_by(cninfo_api_diaoyan.announcementTime.desc()).offset(offset).limit(limit).all()
        total = db_session.query(func.count(cninfo_api_diaoyan.aid)).filter(cninfo_api_diaoyan.secCode == stock_code).scalar()
        report_data = [{
            'secCode': r.secCode,
            'secName': r.secName,
            'announcementTitle': r.announcementTitle,
            'adjunctUrl': r.adjunctUrl,
            'announcementTime': r.announcementTime
        } for r in report_rows]
    
    pages = (total + limit - 1) // limit if limit > 0 else 0
    
    # 获取公司信息 - 优先从研报表获取，如果研报表没有则从问董秘表获取
    if market == 'sh':
        # 从沪市研报表获取公司信息
        company_row = db_session.query(sseinfo_report).filter(sseinfo_report.stock_code == stock_code).first()
        if company_row:
            company_name = company_row.stock_name
        else:
            # 如果研报表没有，尝试从问董秘表获取
            iwen_row = db_session.query(sseinfo_iwen).filter(sseinfo_iwen.stock_code == stock_code).first()
            company_name = iwen_row.stock_name if iwen_row else '未知'
        
        # 获取远程头像URL
        iwen_row = db_session.query(sseinfo_iwen).filter(sseinfo_iwen.stock_code == stock_code).first()
        remote_image_url = iwen_row.stock_image if iwen_row and iwen_row.stock_image else None
    else:
        # 从深市研报表获取公司信息
        company_row = db_session.query(cninfo_api_diaoyan).filter(cninfo_api_diaoyan.secCode == stock_code).first()
        if company_row:
            company_name = company_row.secName
        else:
            # 如果研报表没有，尝试从问董秘表获取
            iwen_row = db_session.query(cninfo_iwen).filter(cninfo_iwen.stock_code == stock_code).first()
            company_name = iwen_row.stock_name if iwen_row else '未知'
        
        # 获取远程头像URL
        iwen_row = db_session.query(cninfo_iwen).filter(cninfo_iwen.stock_code == stock_code).first()
        remote_image_url = iwen_row.stock_image if iwen_row and iwen_row.stock_image else None
    
    # 使用图标管理器获取图标URL（优先本地，不存在时下载）
    company_image = icon_manager.get_icon_url(stock_code, remote_image_url)
    
    company = {
        'stock_code': stock_code,
        'stock_name': company_name,
        'stock_image': company_image,
        'market': market
    }
    
    # 获取问董秘数据 (限20条)
    if market == 'sh':
        iwen_rows = db_session.query(sseinfo_iwen).filter(sseinfo_iwen.stock_code == stock_code).order_by(sseinfo_iwen.question_timestamp.desc()).limit(20).all()
        iwen_total = db_session.query(func.count(sseinfo_iwen.id)).filter(sseinfo_iwen.stock_code == stock_code).scalar()
    else:
        iwen_rows = db_session.query(cninfo_iwen).filter(cninfo_iwen.stock_code == stock_code).order_by(cninfo_iwen.question_timestamp.desc()).limit(20).all()
        iwen_total = db_session.query(func.count(cninfo_iwen.id)).filter(cninfo_iwen.stock_code == stock_code).scalar()
    
    iwen_data = [{
        'question_text': r.question_text,
        'question_time': r.question_time,
        'question_timestamp': r.question_timestamp,
        'answer_text': r.answer_text,
        'answer_time': r.answer_time,
        'answer_timestamp': r.answer_timestamp
    } for r in iwen_rows]
    
    return templates.TemplateResponse("company.html", {
        "request": request,
        "data": report_data,  
        "page": page,
        "pages": pages,
        "company": company,
        "iwen_data": iwen_data,
        "iwen_total": iwen_total,
        "iwen_link": f"/c/{stock_code}/iwen",
        "market": market  # 传递市场信息给模板
    })


@app.get('/report/deep')
async def report_deep_query(page: int = 1, limit: int = 100, db_session: Session = Depends(get_db_session)):
    offset = (page - 1) * limit
    results = db_session.query(report_deep).order_by(
        report_deep.reportDate.desc()).offset(offset).limit(limit).all()
    total = db_session.query(func.count(report_deep.id)).scalar()
    pages = int(total / limit)
    return {
        "code": 200,
        "limit": limit,
        "total": total,
        "pages": pages,
        "data": [result.__dict__ for result in results]
    }


@app.get('/api/iwen')
@app.post('/api/iwen')
async def iwen_api(page: int = 1, limit: int = 20, market: str = "sz", db_session: Session = Depends(get_db_session)):
    offset = (page - 1) * limit
    if market == 'sh':
        q = db_session.query(sseinfo_iwen).order_by(sseinfo_iwen.question_timestamp.desc())
        total = db_session.query(func.count(sseinfo_iwen.id)).scalar()
        rows = q.offset(offset).limit(limit).all()
        data = [row.__dict__ for row in rows]
    elif market == 'sz':
        q = db_session.query(cninfo_iwen).order_by(cninfo_iwen.question_timestamp.desc())
        total = db_session.query(func.count(cninfo_iwen.id)).scalar()
        rows = q.offset(offset).limit(limit).all()
        data = [row.__dict__ for row in rows]
    else:
        total = 0
        data = []
    pages = int(total / limit) if limit else 0
    return {"code": 200, "limit": limit, "total": total, "pages": pages, "data": data}


@app.get('/iwen', response_class=HTMLResponse)
async def iwen_html(request: Request, page: int = 1, limit: int = 20, market: str = "sz", db_session: Session = Depends(get_db_session)):
    offset = (page - 1) * limit
    qa = []
    total = 0
    if market == 'sh':
        rows = db_session.query(sseinfo_iwen).order_by(sseinfo_iwen.question_timestamp.desc()).offset(offset).limit(limit).all()
        total = db_session.query(func.count(sseinfo_iwen.id)).scalar()
        for r in rows:
            stock_code = getattr(r, 'stock_code', '')
            remote_image = getattr(r, 'stock_image', '')
            # 使用图标管理器处理图标
            stock_image = icon_manager.get_icon_url(stock_code, remote_image)
            
            qa.append({
                "stock_code": stock_code,
                "stock_name": getattr(r, 'stock_name', ''),
                "stock_image": stock_image,
                "question_id": getattr(r, 'question_id', ''),
                "question_text": getattr(r, 'question_text', ''),
                "question_time": getattr(r, 'question_time', ''),
                "question_timestamp": getattr(r, 'question_timestamp', 0),
                "answer_id": getattr(r, 'answer_id', ''),
                "answer_text": getattr(r, 'answer_text', ''),
                "answer_time": getattr(r, 'answer_time', ''),
                "answer_timestamp": getattr(r, 'answer_timestamp', 0),
            })
    elif market == 'sz':
        rows = db_session.query(cninfo_iwen).order_by(cninfo_iwen.question_timestamp.desc()).offset(offset).limit(limit).all()
        total = db_session.query(func.count(cninfo_iwen.id)).scalar()
        for r in rows:
            stock_code = getattr(r, 'stock_code', '')
            remote_image = getattr(r, 'stock_image', '')
            # 使用图标管理器处理图标
            stock_image = icon_manager.get_icon_url(stock_code, remote_image)
            
            qa.append({
                "stock_code": stock_code,
                "stock_name": getattr(r, 'stock_name', ''),
                "stock_image": stock_image,
                "question_text": getattr(r, 'question_text', ''),
                "question_time": getattr(r, 'question_time', ''),
                "question_timestamp": getattr(r, 'question_timestamp', 0),
                "answer_text": getattr(r, 'answer_text', ''),
                "answer_time": getattr(r, 'answer_time', ''),
                "answer_timestamp": getattr(r, 'answer_timestamp', 0),
            })
    pages = int(total / limit) if limit else 0
    return templates.TemplateResponse("iwen.html", {"request": request, "data": qa, "page": page, "pages": pages, "market": market})


@app.get('/c/{stock_code:str}/iwen', response_class=HTMLResponse)
async def company_iwen(request: Request, stock_code: str, page: int = 1, limit: int = 20, db_session: Session = Depends(get_db_session)):
    if len(stock_code) != 6 or not stock_code.isdigit():
        return "Invalid stock code"
    
    # 确定市场
    market = 'sh' if stock_code.startswith(('60', '68')) else 'sz'
    
    offset = (page - 1) * limit
    
    if market == 'sh':
        rows = db_session.query(sseinfo_iwen).filter(sseinfo_iwen.stock_code == stock_code).order_by(sseinfo_iwen.question_timestamp.desc()).offset(offset).limit(limit).all()
        total = db_session.query(func.count(sseinfo_iwen.id)).filter(sseinfo_iwen.stock_code == stock_code).scalar()
        qa = [{
            "stock_code": r.stock_code,
            "stock_name": r.stock_name,
            "stock_image": icon_manager.get_icon_url(r.stock_code, r.stock_image),
            "question_id": r.question_id,
            "question_text": r.question_text,
            "question_time": r.question_time,
            "question_timestamp": r.question_timestamp,
            "answer_id": r.answer_id,
            "answer_text": r.answer_text,
            "answer_time": r.answer_time,
            "answer_timestamp": r.answer_timestamp,
        } for r in rows]
    else:
        rows = db_session.query(cninfo_iwen).filter(cninfo_iwen.stock_code == stock_code).order_by(cninfo_iwen.question_timestamp.desc()).offset(offset).limit(limit).all()
        total = db_session.query(func.count(cninfo_iwen.id)).filter(cninfo_iwen.stock_code == stock_code).scalar()
        qa = [{
            "stock_code": r.stock_code,
            "stock_name": r.stock_name,
            "stock_image": icon_manager.get_icon_url(r.stock_code, r.stock_image),
            "question_text": r.question_text,
            "question_time": r.question_time,
            "question_timestamp": r.question_timestamp,
            "answer_text": r.answer_text,
            "answer_time": r.answer_time,
            "answer_timestamp": r.answer_timestamp,
        } for r in rows]
    
    pages = (total + limit - 1) // limit if limit > 0 else 0
    
    return templates.TemplateResponse("iwen.html", {
        "request": request,
        "data": qa,
        "page": page,
        "pages": pages,
        "market": market,
        "stock_code": stock_code  # 用于自定义标题等
    })


@app.get('/api/search')
@app.post('/api/search')
async def search_api(q: str = "", limit: int = 20, db_session: Session = Depends(get_db_session)):
    keyword = (q or "").strip()
    if not keyword:
        return {"code": 200, "q": keyword, "data": {"cninfo": [], "sse_report": [], "iwen": []}}

    like = f"%{keyword}%"

    # cninfo 机构调研（深市）
    cninfo_rows = db_session.query(cninfo_api_diaoyan).filter(
        or_(
            cninfo_api_diaoyan.secCode.like(like),
            cninfo_api_diaoyan.secName.like(like),
            cninfo_api_diaoyan.announcementTitle.like(like)
        )
    ).order_by(cninfo_api_diaoyan.announcementTime.desc()).limit(limit).all()
    cninfo_data = [{
        "secCode": r.secCode,
        "secName": r.secName,
        "title": r.announcementTitle,
        "url": r.adjunctUrl,
        "time": r.announcementTime,
    } for r in cninfo_rows]

    # sseinfo_report（沪市调研）
    sse_rows = db_session.query(sseinfo_report).filter(
        or_(
            sseinfo_report.stock_code.like(like),
            sseinfo_report.stock_name.like(like),
            sseinfo_report.stock_report_name.like(like)
        )
    ).order_by(sseinfo_report.stock_report_timestamp.desc()).limit(limit).all()
    sse_data = [{
        "secCode": r.stock_code,
        "secName": r.stock_name,
        "title": r.stock_report_name,
        "url": r.stock_report_url,
        "time": r.stock_report_timestamp,
    } for r in sse_rows]

    # 问董秘（仅沪市）
    iwen_rows = db_session.query(sseinfo_iwen).filter(
        or_(
            sseinfo_iwen.stock_code.like(like),
            sseinfo_iwen.stock_name.like(like),
            sseinfo_iwen.question_text.like(like),
            sseinfo_iwen.answer_text.like(like)
        )
    ).order_by(sseinfo_iwen.question_timestamp.desc()).limit(limit).all()
    iwen_data = [{
        "stock_code": r.stock_code,
        "stock_name": r.stock_name,
        "question_text": r.question_text,
        "answer_text": r.answer_text,
        "question_timestamp": r.question_timestamp,
        "answer_timestamp": r.answer_timestamp,
    } for r in iwen_rows]

    return {
        "code": 200,
        "q": keyword,
        "data": {
            "cninfo": cninfo_data,
            "sse_report": sse_data,
            "iwen": iwen_data
        }
    }


@app.get('/search', response_class=HTMLResponse)
async def search_page(request: Request, q: str = "", limit: int = 20, db_session: Session = Depends(get_db_session)):
    # 仅聚合三类：cninfo、sseinfo_report、sseinfo_iwen
    result = await search_api(q=q, limit=limit, db_session=db_session)  # type: ignore
    return templates.TemplateResponse("search.html", {"request": request, "q": q, "groups": result.get("data", {})})

@app.get('/wechat/api/sec')
async def wechat_sec_query(page: int = 1, limit: int = 100, db_session: Session = Depends(get_db_session)):
    offset = (page - 1) * limit
    results = db_session.query(wechat_sec).order_by(
        wechat_sec.publish_time.desc()).offset(offset).limit(limit).all()
    total = db_session.query(func.count(wechat_sec.id)).scalar()
    pages = int(total / limit)
    return {
        "code": 200,
        "limit": limit,
        "total": total,
        "pages": pages,
        "data": [result.__dict__ for result in results]
    }

# 公众号-信息安全
# Todo  HTML


@app.get('/wechat/sec', response_class=HTMLResponse)
async def wechat_sec_html(request: Request, page: int = 1, limit: int = 5, db_session: Session = Depends(get_db_session)):
    offset = (page - 1) * limit
    results = db_session.query(wechat_sec).order_by(
        wechat_sec.publish_time.desc()
    ).offset(offset).limit(limit).all()
    total = db_session.query(func.count(wechat_sec.id)).scalar()
    pages = int(total / limit)
    data = [result.__dict__ for result in results]
    return templates.TemplateResponse("wechat_index.html", {"request": request, "data": data, "page": page, "pages": pages})

# 研报


@app.get("/r", response_class=HTMLResponse)
async def deep_report(request: Request, page: int = 1, limit: int = 10000, db_session: Session = Depends(get_db_session)):
    offset = (page - 1) * limit
    results = db_session.query(report_deep).filter(
        report_deep.source == '公众号文章'
    ).order_by(
        report_deep.reportDate.desc()
    ).offset(offset).limit(limit).all()
    total = db_session.query(func.count(report_deep.id)).scalar()
    pages = int(total / limit)
    data = [result.__dict__ for result in results]
    return templates.TemplateResponse("deep.html", {"request": request, "data": data, "page": page, "pages": pages})

# 行业深度研报


@app.get("/deep", response_class=HTMLResponse)
async def deep_report(request: Request, page: int = 1, limit: int = 1000, db_session: Session = Depends(get_db_session)):
    offset = (page - 1) * limit
    results = db_session.query(report_deep).filter(
        report_deep.source == '行业深度报告' and report_deep.file_pagenum != null()
    ).order_by(
        report_deep.reportDate.desc()
    ).offset(offset).limit(limit).all()
    total = db_session.query(func.count(report_deep.id)).scalar()
    pages = int(total / limit)
    data = [result.__dict__ for result in results]
    return templates.TemplateResponse("wechat_report.html", {"request": request, "data": data, "page": page, "pages": pages})

# 上市公司年度报告


@app.get("/r/year_report", response_class=HTMLResponse)
async def report_year(request: Request, page: int = 1, limit: int = 100, db_session: Session = Depends(get_db_session)):
    offset = (page - 1) * limit
    year = datetime.strptime("2023-01-01", "%Y-%m-%d")
    results = db_session.query(year_report).filter(year_report.file_newDate >= year).order_by(year_report.file_newDate.desc()).offset(
        offset).limit(limit).all()
    total = db_session.query(func.count(year_report.file_id)).filter(
        year_report.file_newDate >= year).scalar()
    pages = int(total / limit)
    data = [result.__dict__ for result in results]
    return templates.TemplateResponse("year_report.html", {"request": request, "data": data, "page": page, "pages": pages})
# @app.get("/r/year_report", response_class=HTMLResponse)
# async def report_year(request: Request, page: int = 1, limit: int = 100, db_session: Session = Depends(get_db_session)):
#     offset = (page - 1) * limit
#     results = db_session.query(year_report).order_by(year_report.file_newDate.desc()).offset(
#         offset).limit(limit).all()
#     total = db_session.query(func.count(year_report.file_id)).scalar()
#     pages = int(total / limit)
#     data = [result.__dict__ for result in results]
#     # return {'data': data}
#     return templates.TemplateResponse("year_report.html", {"request": request, "data": data, "page": page, "pages": pages})


# Todo:
# 顶部导航页  机构调研   研报
# 沪市主板  科创板   创业板   中小板  北交所
if __name__ == '__main__':
    # uvicorn.run('main:app', reload=True, host='0.0.0.0', port=9999)
    uvicorn.run('main:app', reload=True, host='127.0.0.1', port=9999)
